import pandas as pd


def dm_info(sql: str) -> pd.DataFrame:
    with open(sql, 'r', encoding='utf-8') as file:
        sql_content = file.read()

    sql_split = [item.strip() for item in sql_content.split("CREATE TABLE \"KNOWLEDGE\".") if item and item.strip()]
    dm_table_field = pd.DataFrame(columns=['uid', '表名DM', '字段DM', '完整类型DM', '类型DM', '长度DM'])
    for sql_one in sql_split:
        # print("=========================")
        fieldArr = [item.strip() for item in sql_one.split("\n") if item and item.strip() and item.strip().startswith("\"")]
        print(f"表名{fieldArr[0]}")
        tableName = fieldArr[0].replace("\"", "").strip()

        print(fieldArr)
        for i in range(1, len(fieldArr)):
            fieldOneSplit = fieldArr[i].split(" ")
            fieldName = fieldOneSplit[0].replace("\"", "").strip()
            fieldTypeFull = fieldOneSplit[1].replace(",", "").strip()
            strip_split = fieldTypeFull.split("(")
            dm_table_field.loc[len(dm_table_field)] = [f"{tableName}.{fieldName}",
                                                       tableName,
                                                       fieldName,
                                                       fieldTypeFull,
                                                       strip_split[0],
                                                       strip_split[1].replace(")", "") if len(strip_split) > 1 else ""]

    dm_field_comment = pd.DataFrame(columns=['uid', '字段描述DM'])
    comment_arr = [item.strip() for item in sql_content.split("\n") if item and item.strip() and item.strip().startswith("COMMENT ON COLUMN \"KNOWLEDGE\".")]
    for comment_one in comment_arr:
        c_table = comment_one.split(".")[1].replace("\"", "")
        c_column = comment_one.split(".")[2].split(" IS ")[0].replace("\"", "")
        c_comment = comment_one.split(".")[2].split(" IS ")[1].split("\'")[1]
        # print(f"{c_table}\t{c_column}\t{c_comment}")
        dm_field_comment.loc[len(dm_field_comment)] = [f"{c_table}.{c_column}", c_comment]

    dm_table_comment = pd.DataFrame(columns=['表名DM', '表描述DM'])
    table_comment_arr = [item.strip() for item in sql_content.split("\n") if item and item.strip() and item.strip().startswith("COMMENT ON TABLE \"KNOWLEDGE\".")]
    for comment_one in table_comment_arr:
        c_table = comment_one.split(".")[1].split(" ")[0].replace("\"", "")
        c_comment = comment_one.split(".")[1].split("\'")[1]
        dm_table_comment.loc[len(dm_table_comment)] = [c_table, c_comment]

    dm_table_field = dm_table_field.merge(dm_field_comment, how='left', on=['uid'])
    dm_table_field = dm_table_field.merge(dm_table_comment, how='left', on=['表名DM'])
    return dm_table_field

if __name__ == '__main__':
    dm_data = dm_info('dm.sql')
    dm_data.to_excel("gen/达梦数据库.xlsx", index=False)
    print(dm_data.head(100))

    dm_table = dm_data[['表名DM', '表描述DM']]
    dm_table = dm_table.drop_duplicates(subset=['表名DM'])
    # 导出到 table.xlsx 文件
    dm_table.to_excel('gen/dm_table.xlsx', index=False)

    str_update_sql = ''

    for i, row in dm_table.iterrows():
        dm_: str = row['表名DM']
        if not (dm_.startswith('IM_AGENT') or dm_.startswith('BAK_') or dm_.startswith('ZZZ_')):
            str_update_sql += f'ALTER TABLE "{dm_}" RENAME TO "ZZZ_{dm_}";\n\n'
            print(dm_)

    with open('gen/rename_table.sql', 'w', encoding='utf-8') as file_w:
        file_w.write(str_update_sql)
